#
# Copyright (c) 2006-2009 National ICT Australia (NICTA), Australia

# Copyright (c) 2004-2009 - WINLAB, Rutgers University, USA
#
# Permission is hereby granted, free of charge, to any person obtaining a copy
# of this software and associated documentation files (the "Software"), to deal
# in the Software without restriction, including without limitation the rights
# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
# copies of the Software, and to permit persons to whom the Software is
# furnished to do so, subject to the following conditions:
#
# The above copyright notice and this permission notice shall be included in
# all copies or substantial portions of the Software.
#
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.  IN NO EVENT SHALL THE
# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
# THE SOFTWARE.
#
#
# = postgreSQLInventory.rb
#
# == Description
#
# This file defines the PostgreSQLInventory class.
#

require 'pg'
require 'set'

#
# This class implements an interface to the PostgreSQL database which holds the
# Inventory information for the available testbeds. This class is used by the
# Inventory GridServices as a 'query engine' over the PostgreSQL database.
# Some specific queries to the Inventory have their methods implemented here.
#
#
class PostgreSQLInventory < MObject

  #
  # Create a new PostgreSQLInventory instance
  #
  # - host = address of the PostgreSQL server hosting the Inventory database
  # - user = user to access this database
  # - password = password to access this database
  # - database = name of the database (default = LaboraScheduler)
  # - port = database port (postgresql default port = 5432)
  #
  def initialize(host, user, password, database = "LaboraScheduler", database_port = 5432)
    @connected = false
    @my    = nil
    @host  = host
    @user  = user
    @pw    = password
    @db    = database
    @port  = database_port
    open()
  end

  #
  # Open a connection to the PostgreSQL Server, using the parameters given when this
  # PostgreSQLInventory was created, see initialize(...).
  #
  def open()
    begin
      @my = PGconn.connect(@host, @port, '', '', @db, @user, @pw)
      debug " -  Open Connection to Postgres server"
      @connected = true
    rescue StandardError => e
      debug "SQL error message: #{e.error}."
    end
  end

  #
  # Close a previously opened connection to the PostgreSQL server
  #
  def close()
    @connected = false
    @my.close
    @my = nil
  end

  #
  # Run a given Query against the PostgreSQL database, and execute a given
  # block of command on the result of this query
  #
  # - query = a String with the PostgreSQL query to run
  # - &block = the block of command, which will process the result of this query
  #
  def runQuery(query, &block)
    raise "Trying to run a query when not connected to inventory DB" if not @connected
    begin
      debug "SQL Query: '#{query}'"
      reply = @my.exec(query)
      # Check if the SQL result contains anything at all...
      # If so, then call the block of commands to process it
      if (reply.count > 0)
        reply.each() { |result|
          result = result.first if result.class==Array
          result = result.to_s
          debug "SQL Reply: '#{result.to_s}'"
          yield(result)
        }
      else
        debug "SQL Reply is EMPTY!"
      end
    rescue StandardError => e
      debug "ERROR - SQL Error: '#{e}'"
    end
  end

  #
  # Query the Inventory database for the Control IP address of a specific node
  #
  # - hrn = HRN of the node to query
  # - domain = name of the testbed to query (default=grid)
  #
  # [Return] the Control IP address of the node matching the query
  #
  def getControlIP(hrn, domain = "grid")
    qs = <<CONTROL_QS
SELECT node.control_ip FROM node WHERE node.hrn='#{hrn}';
CONTROL_QS

    addr = nil
    runQuery(qs) { |ip|
      addr = ip
    }
    return addr
  end

  #
  # Query the Inventory database for the CMC IP address of a specific node
  #
  # - hrn = HRN of the node to query
  # - domain = name of the testbed to query (default=grid)
  #
  # [Return] the CMC IP address of the node matching the query
  #
  def getCmcIP(hrn, domain = "grid")
    qs = <<CMC_QS
SELECT node.cmc_ip FROM node WHERE node.hrn='#{hrn}';
CMC_QS

    addr = nil
    runQuery(qs) { |ip|
      addr = ip
    }
    return addr
  end

  #
  # Query the Inventory database for the HRN of a specific node
  #
  # - hostname = hostname of the node to query
  # - domain = name of the testbed to query (default=grid)
  #
  # [Return] the HRN of the node matching the query
  #
  def getHRN(hostname, domain = "grid")
    qs = <<HRN_QS
SELECT node.hrn FROM node WHERE node.hostname='#{hostname}';
HRN_QS

      addr = nil
      runQuery(qs) { |h|
        hrn = h
      }
      return hrn
    end

  #
  # Query the Inventory database for the default disk of a specific node
  #
  # - hrn = hrn of the node to query
  # - domain = name of the testbed to query (default=grid)
  #
  # [Return] the default disk of the node matching the query
  #
  def getDefaultDisk(hrn, domain = "grid")
    qs = <<DD_QS
SELECT node.disk FROM node WHERE node.hrn='#{hrn}';
DD_QS

    disk = nil
    runQuery(qs) { |d|
      disk = d
    }
    return disk
  end

  #
  # Query the Inventory database for the switch IP address and switch port of a specific node
  # OBS: not implemented in Labora Scheduler
  #
  # - hrn = HRN of the node to query
  # - domain = name of the testbed to query (default=grid)
  #
  # [Return] switch ip:port of the node matching the query
  #
  def getSwitchPort(hrn, domain = "grid")
    qs = <<SWITCH_QS
SELECT locations.switch_ip, locations.switch_port
  FROM locations
  LEFT JOIN nodes ON locations.id = nodes.location_id
  LEFT JOIN testbeds ON locations.testbed_id = testbeds.id
WHERE testbeds.name='#{domain}'
  AND nodes.hrn='#{hrn}';
SWITCH_QS
	
	return nil;
	
    addr = nil
    runQuery(qs) { |ip, port|
      addr = "#{ip}:#{port}" if !ip.empty? && !port.empty?
    }
    return addr
  end

  #
  # Query the Inventory database for the name of the PXE image being that should
  #
  # - hrn = HRN of the node to query
  # - domain = name of the testbed to query (default=grid)
  #
  # [Return] a String with the name of PXE image to use for the node matching
  #          the query
  #
  def getNodePXEImage(hrn, domain = "grid")
  qs = <<PXEIMAGE_QS
SELECT pxeimages.image_name
  FROM pxeimages
  LEFT JOIN node ON pxeimages.id = node.pxeimage_id
WHERE node.hrn='#{hrn}'
PXEIMAGE_QS

    imageName = nil
    runQuery(qs) { |name|
      imageName = name
    }
    return imageName
  end

  #
  # Query the Inventory database for the names of all resources, which
  # are available for a given testbed.
  #
  # - domain = name of the testbed to query (default=grid)
  #
  # [Return] a Set with the names of all the resources
  #
  def getAllResources(domain = "*")
    qs = <<ALLRESOURCES_QS
SELECT node.hrn FROM node;
ALLRESOURCES_QS

    resources = Set.new
    runQuery(qs) { |name|
      resources.add(name)
    }
    return resources
  end

  def getAllTestbeds
  qs = <<ALLTESTBEDS_QS
SELECT name FROM testbed;
ALLTESTBEDS_QS

    result = Array.new
    begin
      @my.exec(qs).each() { | n |
           result << n
    }
    rescue StandardError => e
      err_str = "Inventory - Could not execute query in getAllTestbeds"
      p err_str
      MObject.debug err_str
    end
    result
  end

  def getAllNodes
  qs = <<ALLNODES_QS
SELECT hostname, hrn, control_mac, control_ip, disk, testbed.name FROM node, testbed;
ALLNODES_QS

    result = Array.new
    begin
      @my.exec(qs).each() { | hostname,hrn,control_mac,control_ip,disk,tbname |
           result << {'name' => "#{hostname}", 'hrn' => "#{hrn}", 'control_mac' => "#{control_mac}",
             'control_ip' => "#{control_ip}", 'disk' => "#{disk}",
             'testbed' => "#{tbname}"}
    }
    rescue StandardError => e
      err_str = "Inventory - Could not execute query in getAllTestbeds"
      p err_str
      MObject.debug err_str
    end
    result
  end

  # Deprecated  functions in LaboraScheduler
  def addTestbed(testbed)
    return nil;
    qs = "INSERT INTO testbed (name) VALUES ('#{testbed}');"
    begin
      @my.exec(qs)
    rescue StandardError => e
      err_str = "Inventory - Could not execute query in rmTestbed: '#{qs}'"
      p err_str
      MObject.debug err_str
    end
    return @my.affected_rows > 0
  end

  def editTestbed(testbed, name)
    return nil;
    qs = "UPDATE testbed SET name = '#{name}' WHERE name = '#{testbed}';"
    begin
      @my.exec(qs)
    rescue StandardError => e
      err_str = "Inventory - Could not execute query in editTestbed: '#{qs}'"
      p err_str
      MObject.debug err_str
    end
    return @my.affected_rows > 0
  end

  def rmTestbed(testbed)
    return nil;
    qs = "DELETE FROM testbed WHERE name = '#{testbed}';"
    begin
      @my.exec(qs)
    rescue StandardError => e
      err_str = "Inventory - Could not execute query in rmTestbed: '#{qs}'"
      p err_str
      MObject.debug err_str
    end
    return @my.affected_rows > 0
  end

  def addNode(node)
    return nil;
    qs = "INSERT INTO node (#{node.keys.join(',')}) VALUES ('#{node.values.join('\',\'')}');"
    MObject.debug(qs)
    return true
    begin
      @my.exec(qs)
    rescue StandardError => e
      err_str = "Inventory - Could not execute query in rmTestbed: '#{qs}'"
      p err_str
      MObject.debug err_str
    end
    return @my.affected_rows > 0
  end

  def rmNode(node,testbed)
    return nil;
    qs = "DELETE FROM node WHERE name = '#{node}';"
    begin
      @my.exec(qs)
    rescue StandardError => e
      err_str = "Inventory - Could not execute query in rmNode: '#{qs}'"
      p err_str
      MObject.debug err_str
    end
    return @my.affected_rows > 0
  end

end

